/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accessor;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.Model;

/**
 *
 * @author QUYET
 */
public class UserAccessor extends Model{

    public UserAccessor() {
    }
    public User getUserLogin(String email,String pass){
        User user = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "Select * from tblUsers where email = ? and password = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, email);
            ps.setString(2, pass);
            rs = ps.executeQuery();
            if(rs.next()){
                user = new User();
                user.setUserID(rs.getInt("userID"));
                user.setEmail(rs.getString("email"));
                user.setPassword(rs.getString("password"));
                user.setDepartmentID(rs.getInt("departmentID"));
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return user;
    }
    
    public User getUserByID(int userID){
        User user = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "Select tblUsers.active,tblUsers.password,tblUsers.email,tblUsers.lastLoginDate,tblUsers.DepartmentID,tblUserProfile.*,tblDepartment.departmentName from tblUsers"
                    + " inner join tblUserProfile "
                    + " on tblUsers.userID = tblUserProfile.userID"
                    + " inner join tblDepartment "
                    + " on tblUsers.departmentID = tblDepartment.departmentID"
                    + " where tblUsers.userID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);
            rs = ps.executeQuery();
            if(rs.next()){
                user = new User();
                user.setUserID(rs.getInt("userID"));
                user.setEmail(rs.getString("email"));
                user.setFirstName(rs.getString("firstName"));
                user.setLastName(rs.getString("lastName"));
                user.setDepartmentID(rs.getInt("departmentID"));
                user.setDepartmentName(rs.getString("departmentName"));
                user.setLastLoginDate(rs.getDate("lastLoginDate"));
                user.setDateOfBirth(rs.getDate("dateOfBirth"));
                user.setActive(rs.getInt("active"));
                user.setPassword(rs.getString("password"));
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return user;
    }
    
    public int insertUser(User user){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Insert into tblUsers values(?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getEmail());
            ps.setString(2, user.getPassword());
            ps.setInt(3, user.getDepartmentID());
            ps.setDate(4, new Date(System.currentTimeMillis()));
            ps.setDate(5, new Date(System.currentTimeMillis()));
            ps.setInt(6, 1);
            ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
            
            retVal = getInsertID();
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    
    protected int getInsertID() {
        int id = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("Select TOP 1 * from tblUsers order by UserID DESC");
            rs = ps.executeQuery();
            if (rs.next()) {
                id = rs.getInt("UserID");
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return id;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }
    }
    
    public int insertUserProfile(User user){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Insert into tblUserProfile values(?,?,?,?,getdate())";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, user.getUserID());
            ps.setString(2, user.getFirstName());
            ps.setString(3, user.getLastName());
            ps.setDate(4, user.getDateOfBirth());
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    
    public int updateUserDepartment(User user){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Update tblUsers set departmentID = ? Where userID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, user.getDepartmentID());
            ps.setInt(2, user.getUserID());
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
            return retVal;
        } catch (Exception e) {
            e.printStackTrace();
            return retVal;
        }
    }
    public int updateUserProfile(User user){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Update tblUserProfile set firstName = ?, lastName = ?, dateOfBirth = ? Where userID = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getFirstName());
            ps.setString(2, user.getLastName());
            ps.setDate(3, user.getDateOfBirth());
            ps.setInt(4, user.getUserID());
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
            return retVal;
        } catch (Exception e) {
            e.printStackTrace();
            return retVal;
        }
    }
    public int deleteUser(int userID){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Delete tblUsers Where userID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    public int deleteUserProfile(int userID){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Delete tblUserProfile Where userID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    public int changePass(User user){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Update tblUsers set password = ? Where userID = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getPassword());
            ps.setInt(2, user.getUserID());
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    
    public List<User> getAllUser(){
        List<User> retVal = new ArrayList<User>();
        User user = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "Select tblUsers.active,tblUsers.email,tblUsers.lastLoginDate,tblUsers.DepartmentID,tblUserProfile.*,tblDepartment.departmentName from tblUsers"
                    + " inner join tblUserProfile "
                    + " on tblUsers.userID = tblUserProfile.userID"
                    + " inner join tblDepartment "
                    + " on tblUsers.departmentID = tblDepartment.departmentID"
                    + " where tblUsers.DepartmentID <> 1";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                user = new User();
                user.setUserID(rs.getInt("userID"));
                user.setEmail(rs.getString("email"));
                user.setFirstName(rs.getString("firstName"));
                user.setLastName(rs.getString("lastName"));
                user.setDepartmentID(rs.getInt("departmentID"));
                user.setDepartmentName(rs.getString("departmentName"));
                user.setLastLoginDate(rs.getDate("lastLoginDate"));
                user.setActive(rs.getInt("active"));
                
                retVal.add(user);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return retVal;
    }
    
    public int activeUser(int userID){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "update tblUsers set active = 1 Where userID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    public int deactiveUser(int userID){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "update tblUsers set active = 0 Where userID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    public boolean isEmailExist(String email) {
        boolean isExist = false;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("Select * from tblUsers Where email = ?");
            ps.setString(1, email);
            rs = ps.executeQuery();
            if (rs.next()) {
                isExist = true;
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return isExist;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
    }
}
